package com.show.service.event;

import com.show.dao.event.EventRepositoty;
import com.show.dao.public_security.PublicSecurityRepositoty;
import com.show.dto.Event;
import com.show.dto.activiti.TaskBean;
import com.show.dto.kv.KV;
import com.show.dto.message_acessory.MessageAccessory;
import com.show.dto.public_security.PublicSecurity;
import com.show.dto.sys_emp.SysEmp;
import com.show.dto.sys_emp.SysEmpOrg;
import com.show.dto.sys_emp.SysEmpRole;
import com.show.dto.sys_org.SysOrg;
import com.show.service.eact.EactServer;
import com.show.service.emp.EmpOrgService;
import com.show.service.emp.EmpService;
import com.show.service.grid_owner.GridOwnerService;
import com.show.service.kv.KVService;
import com.show.service.message_acessory.MessageAccessoryService;
import com.show.service.org.SysOrgService;
import com.show.util.FileUtil;
import com.show.util.PageUtil;
import com.show.util.QueryUtil;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;

/**
 * @author 王硕
 * @Param
 * @date 2018-1-29 9:13
 */
@Service
public class EventService {
    @Autowired
    private EventRepositoty eventRepositoty;
    @Autowired
    private QueryUtil queryUtil;
    @Autowired
    private EactServer eactServer;
    @Autowired
    private EmpService empService;
    @Autowired
    private EmpOrgService empOrgService;
    @Autowired
    private SysOrgService sysOrgService;
    @Autowired
    private MessageAccessoryService messageAccessoryService;
    @Autowired
    private KVService kVService;
    @Autowired
    private PublicSecurityRepositoty publicSecurityRepositoty;
    @Autowired
    private FileUtil fileUtil;
    @Autowired
    private GridOwnerService gridOwnerService;

    /**
     * Event-save
     *
     * @author 姜文
     * @Param
     * @date 2018年3月8日, PM 05:58:56
     */
    public Integer insertEvent(Event event,String insertType) {
        event.setCdate(new Date());
        event.setUpdateTime(new Date());
        if(insertType=="pc"){
            event.setOutcome("协助维护");
        }
        if("app".equals(insertType)){
            //查询当前上报事件网格员所在网格grid_id
            Integer empId = event.getEmpId();
            Integer gridId= gridOwnerService.queryByEmpId(empId).get(0).getGridId();
            if(null != gridId){
                event.setGridId(gridId);
            }
            event.setEventSource(16);
            event.setEventCode(this.getEventCode2());
        }else{
            event.setGridId(event.getGridId());
            event.setEventSource(55);
            event.setEventCode(this.getEventCode());
        }

        KV kv = kVService.getKVByVal(event.getOutcome()).get(0);
        event.setEventTypeId(kv.getKvId());
        if ("上报社区".equals(event.getOutcome())) {event.setEventReportType(0);}
        if ("直报街道".equals(event.getOutcome())) {event.setEventReportType(1);}
        try {
            Event event1 = eventRepositoty.save(event);
            return event1.getEventId();
        } catch (Exception e) {
            return -1;
        }

    }

    /**
     * PublicSecurity-save
     *
     * @author 姜文
     * @Param
     * @date 2018年3月8日, PM 05:58:56
     */
    public Integer insertPublicSecurity(Event event, String insertType) {
        PublicSecurity publicSecurity = new PublicSecurity();
        publicSecurity.setEmpId(event.getEmpId());
        publicSecurity.setEventContent(event.getEventContent());
        publicSecurity.setEventTitle(event.getEventTitle());
        publicSecurity.setEventLocation(event.getEventLocation());
        publicSecurity.setEventSource(55);
        publicSecurity.setEventStatus(0);
        publicSecurity.setGridId(event.getGridId());
        publicSecurity.setLat(event.getLat());
        publicSecurity.setLng(event.getLng());
        publicSecurity.setCdate(new Date());
        publicSecurity.setUpdateTime(new Date());
        publicSecurity.setMessageAccessoryIds(null);
        if("pc".equals(insertType)){
            publicSecurity.setEventCode(this.getEventCode());
            event.setGridId(event.getGridId());
        }else{
            publicSecurity.setEventCode(this.getEventCode2());
            Integer empId = event.getEmpId();
            Integer gridId= gridOwnerService.queryByEmpId(empId).get(0).getGridId();
            if(null != gridId){
                event.setGridId(gridId);
            }
        }
        try {
            PublicSecurity publicSecurity1 = publicSecurityRepositoty.save(publicSecurity);
            return publicSecurity1.getPublicSecurityId();
        } catch (Exception e) {
            return -1;
        }
    }

    /**
     * code拼接——event
     *
     * @author 姜文
     * @Param
     * @date 2018年3月8日, PM 05:58:56
     */
    public String getEventCode() {
        SimpleDateFormat sdf = new SimpleDateFormat("yyyyMM"); //格式化
        String nowStr = "PC[" + sdf.format(new Date()) + "]"; //获取当前系统时间拼接串
        List<Event> eventList = this.findAllForCode();//获得最新事件
        String eventStr;
        if (eventList.size() == 0) {
            eventStr = nowStr + "000";
        } else {
            eventStr = eventList.get(0).getEventCode();//获得最新事件的code
        }
        String str10 = eventStr.substring(0, 10); //截取字符串 固定前10—PC[201801]—进行比较
        String strEnd = eventStr.substring(10, eventStr.length()); //截取字符串 后三位 递增拼接
        //拆串 将最新事件的code与系统时间比较 如果一样就递增 不一样最新
        return codePinJie(str10, nowStr, strEnd);
    }

    /**
     * code拼接——PublicSecurity
     *
     * @author 王硕
     * @Param
     * @date 2018年3月8日, PM 05:58:56
     */
    public String getEventCode2() {
        SimpleDateFormat sdf = new SimpleDateFormat("yyyyMM"); //格式化
        String nowStr = "APP[" + sdf.format(new Date()) + "]"; //获取当前系统时间拼接串
        List<PublicSecurity> eventList = this.findAllForCode2();//获得最新事件
        String eventStr;
        if (eventList.size() == 0) {
            eventStr = nowStr + "000";
        } else {
            eventStr = eventList.get(0).getEventCode();//获得最新事件的code
        }
        String str10 = eventStr.substring(0, 10); //截取字符串 固定前10—PC[201801]—进行比较
        String strEnd = eventStr.substring(10, eventStr.length()); //截取字符串 后三位 递增拼接
        //拆串 将最新事件的code与系统时间比较 如果一样就递增 不一样最新
        return codePinJie(str10, nowStr, strEnd);
    }

    /**
     * code拼接
     *
     * @author 姜文
     * @Param
     * @date 2018年3月8日, PM 05:58:56
     */
    public String codePinJie(String strStrat, String nowStr, String strEnd){
        String codeStr;
        if (strStrat.equals(nowStr)) {
            int code = Integer.valueOf(strEnd) + 1;
            if (code > 99) {
                codeStr = nowStr + code;
            } else if (code > 9) {
                codeStr = nowStr + "0" + code;
            } else {
                codeStr = nowStr + "00" + code;
            }
        } else {
            codeStr = nowStr + "001";
        }
        return codeStr;
    }

    /**
     * 获取下一步执法角色
     *
     * @param outcome
     * @param assignee
     * @return
     */
    public String getAssigne(String outcome, String assignee, String orgId) {
        if ("上报街道".equals(outcome)) {
            if (assignee.indexOf(",") == -1) {
                //只选择一个街道
                assignee = assignee + ".17";  //社区选择街道上报
            } else {
                //选择多个街道
                String assignees = "";
                for (String str : assignee.split(",")) {
                    str += ".17,";
                    assignees += str;
                }
                assignee = assignees.substring(0, assignees.length() - 1);
            }
        } else if ("派遣执办".equals(outcome)) {
            //assignee 为下派执法人员集合  empId,empId,empId
        } else if ("执办完结".equals(outcome)) {
            assignee = orgId + ".18";     //社区信息员  当前登陆人所在部门
        } else if ("社区办结".equals(outcome)) {
            assignee = "17";   //街道信息员
        } else if ("交办社区".equals(outcome)) {
            if (assignee.indexOf(",") == -1) {
                //只选择一个社区
                assignee = assignee + ".18";  //街道选择一个社区下派
            } else {
                //选择多个社区
                String assignees = "";
                for (String str : assignee.split(",")) {
                    str += ".18,";
                    assignees += str;
                }
                assignee = assignees.substring(0, assignees.length() - 1);
            }
        } else if ("执办完成".equals(outcome)) {
            assignee = orgId + ".18";     //社区信息员   当前登陆人所在部门
        }

        return assignee;
    }

    public void deleteEvent(Event event) {
        eventRepositoty.delete(event);
    }

    /**
     * 根据的当前登陆人角色查看组任务
     *
     * @param empId
     * @param page
     * @param pageSize
     * @return
     */
    public PageUtil<Event> queryEventByIds(Integer empId, Integer page, Integer pageSize) {
        if (null == page || "".equals(page) || "0".equals(page)) {//排空，设置默认值
            page = 1;
        }
        if (null == pageSize || "".equals(pageSize) || "0".equals(pageSize)) {//排空，设置默认值
            pageSize = 10;
        }
        String strStart = "'";   //要求字符串头
        String strEnd = "'";     //要求字符串尾
        String roleIds = "";     //角色ID集合  用  ， 分隔
        String eventIds = "";
        String index = "'0'";      //单一角色时候 多拼接一个
        String orgId = empOrgService.getOrgByempId(empId).get(0).getOrgId();
        String roleIdss = empService.findEmpRoleIds(empId);
        Integer pOrgId = sysOrgService.getParentOrgId(orgId);
        String orgRole = "";
        if (roleIdss != "" && roleIdss != null) {
            for (String roleId : roleIdss.split(",")) {
                //判断该登陆人是否含有社区信息员角色
                if ("21".equals(roleId)) {
                    // 按照 部门 id   .  角色 id     orgId.roleId
                    roleId = orgId + "." + roleId;
                }
                //判断该登陆人是否含有街道信息员角色
                if ("17".equals(roleId)) {
                    // 按照 部门 id   .  角色 id     orgId.roleId
                    roleId = orgId + "." + roleId;
                }
                roleIds += "'" + roleId + "',";
            }
        }
        roleIds = roleIds + "'0'";//角色集合
        List<TaskBean> tasklist = eactServer.findGroupList(roleIds);    // 返回taskId  集合
        for (TaskBean taskBean : tasklist) {
            eventIds += strStart + taskBean.getId() + strEnd + ",";
        }
        try {
            eventIds = eventIds.substring(0, eventIds.length() - 1);
        } catch (Exception e) {
            eventIds = "'0'";
        }
//        String sql = "SELECT * FROM `event` where event_id in (" + eventIds + ") ORDER BY cdate DESC ";
        String sql = "SELECT\n" +
                "\te.event_id AS eventId,\n" +
                "\te.emp_id AS empId,\n" +
                "\tse.emp_name AS empName,\n" +
                "\te.event_code AS eventCode,\n" +
                "\te.event_content AS eventContent,\n" +
                "\te.event_title AS eventTitle,\n" +
                "\te.event_location AS eventLocation,\n" +
                "\tkv.val AS val,\n" +
                "\tg.grid_name AS gridName,\n" +
                "\te.lat AS lat,\n" +
                "\te.lng AS lng,\n" +
                "\te.cdate AS cdate,\n" +
                "\te.update_time AS updateTime\n" +
                "FROM\n" +
                "\t`event` e\n" +
                "LEFT JOIN kv kv ON e.event_source = kv.kv_id\n" +
                "LEFT JOIN sys_emp se ON e.emp_id = se.emp_id\n" +
                "LEFT JOIN grid g ON e.grid_id = g.grid_id\n" +
                "WHERE\n" +
                "\tevent_id IN (" + eventIds + ")\n" +
                "ORDER BY\n" +
                "\te.cdate DESC";
        PageUtil<Event> event = queryUtil.queryByPage(sql, Event.class, page, pageSize);
        for (Event ev : event.getContent()) {
            if (ev.getEmpId() != null) {
                ev.setEmpName(empService.findOne(ev.getEmpId()).getEmpName());
            } else {
                ev.setEmpName("无法获取");
            }
        }
        return event;
    }

    /**
     * 根据当前登陆人角色查询个人任务List集合
     *
     * @param empId
     * @param page
     * @param pageSize
     * @return
     */

    public PageUtil taskListByUserId(String empId, Integer page, Integer pageSize) {
        if (null == page || "".equals(page) || "0".equals(page)) {//排空，设置默认值
            page = 1;
        }
        if (null == pageSize || "".equals(pageSize) || "0".equals(pageSize)) {//排空，设置默认值
            pageSize = 10;
        }
        List<TaskBean> tasklist = eactServer.findTaskList(empId.toString());  // 返回taskId  集合
        String eventIds = this.getEventIds(empId);
//        String sql = "SELECT * FROM `event` where event_id in (" + eventIds + ") ORDER BY cdate DESC ";
        String sql = "SELECT\n" +
                "\te.event_id AS eventId,\n" +
                "\te.emp_id AS empId,\n" +
                "\tse.emp_name AS empName,\n" +
                "\te.event_code AS eventCode,\n" +
                "\te.event_content AS eventContent,\n" +
                "\te.event_title AS eventTitle,\n" +
                "\te.event_location AS eventLocation,\n" +
                "\tkv.val AS val,\n" +
                "\tg.grid_name AS gridName,\n" +
                "\te.lat AS lat,\n" +
                "\te.lng AS lng,\n" +
                "\te.cdate AS cdate,\n" +
                "\te.update_time AS updateTime\n" +
                "FROM\n" +
                "\t`event` e\n" +
                "LEFT JOIN kv kv ON e.event_source = kv.kv_id\n" +
                "LEFT JOIN sys_emp se ON e.emp_id = se.emp_id\n" +
                "LEFT JOIN grid g ON e.grid_id = g.grid_id\n" +
                "WHERE\n" +
                "\tevent_id IN (" + eventIds + ")\n" +
                "ORDER BY\n" +
                "\te.cdate DESC";
        PageUtil<Event> event = queryUtil.queryByPage(sql, Event.class, page, pageSize);
        if (event.getContent() != null) {
            List<Event> eventList = event.getContent();
            for (Event event2 : eventList) {
                for (TaskBean taskBean : tasklist) {
                    if (taskBean.getId().equals(event2.getEventId().toString())) {
                        event2.setOutcomeList(taskBean.getOutcomeList());
                        event2.setTaskId(taskBean.getTaskId());
                    }
                }
            }
            event.setContent(eventList);
        }
        for (Event ev : event.getContent()) {
            if (ev.getEmpId() != null) {
                ev.setEmpName(empService.findOne(ev.getEmpId()).getEmpName());
            } else {
                ev.setEmpName("无法获取");
            }
        }

        return event;
    }

    /**
     * 根据empid查roleIds，循环查询findGroupList
     */
    public String getEventIds(String empId) {
        String strStart = "'";   //要求字符串头
        String strEnd = "'";     //要求字符串尾
        List<TaskBean> tasklist = eactServer.findTaskList(empId.toString());    // 返回taskId  集合
        String eventIds = "";
        for (TaskBean taskBean : tasklist) {
            eventIds += strStart + taskBean.getId() + strEnd + ",";
        }
        try {
            eventIds = eventIds.substring(0, eventIds.length() - 1);
        } catch (Exception e) {
            eventIds = "'0'";
        }
        return eventIds;
    }

    /**
     * 获取事件数量
     *
     * @return
     */
    public Integer getEventnum() {
        return eventRepositoty.getEventNum();
    }

    /**
     * 获取事件列表
     *
     * @return
     */
    public List<Event> getEventList() {
        return eventRepositoty.getEventList();
    }

    /**
     * 交办执办 选择人员树
     *
     * @author 姜文
     * @Param
     * @date 2018年2月26日, PM 02:56:42
     */
    public Object getEmpTree(String orgId) {
        String sql = "SELECT\n" +
                "\tse.emp_id AS id,\n" +
                "\tse.emp_name AS `name`\n" +
                "FROM\n" +
                "\t`sys_emp_org` seo\n" +
                "LEFT JOIN sys_emp se ON seo.emp_id = se.emp_id\n" +
                "LEFT JOIN sys_org so ON seo.org_id = so.org_id\n" +
                "WHERE\n" +
                "\tseo.org_id in (" + orgId + ")";
        return queryUtil.query(sql, SysEmpOrg.class);
    }

    /**
     * 根据部门获取当前部门下的执法人员
     *
     * @return
     */
    public List<SysEmp> LawEnforcementForTree(String orgIds) {
        String sql = "SELECT DISTINCT se.emp_id AS orgId, se.emp_name AS orgName from sys_emp se LEFT JOIN sys_emp_org USING(emp_id)  LEFT JOIN sys_emp_role USING(emp_id) where org_id in (" + orgIds + ") and role_id = 20\n";
        return queryUtil.query(sql, SysEmp.class);
    }

    /*
     *
     * @author 王玉涛
     * @Date 2018/3/6 15:45
     * @param
     * @return
     */
    public Object updateEvent(Event event) {
        return eventRepositoty.saveAndFlush(event);
    }

    /*
     *
     * @author 王玉涛
     * @Date 2018/3/6 15:45
     * @param
     * @return
     */
    public Event getEvent(String id) {
        return eventRepositoty.findOne(id);
    }

    /**
     * 储存附件 获得附件Ids
     *
     * @author 姜文
     * @Param
     * @date 2018年3月13日, AM 11:30:07
     */
    public String saveMessageAccessory(String imgStr, String audioStr, String vedioStr, String eventId) throws ParseException {
        String messageAccessoryIds = "";
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        String time = sdf.format(new Date());
        Date date = sdf.parse(time);
        if (imgStr != null && !"".equals(imgStr)) {
            MessageAccessory messageAccessory = new MessageAccessory();
            messageAccessory.setAccessoryType("1");
            messageAccessory.setAccessoryName(imgStr);
            messageAccessory.setAccessoryPath(fileUtil.getShowUrl()+imgStr);
            messageAccessory.setEventId(eventId);
            messageAccessory.setCdate(date);
            Integer messageAccessoryId = messageAccessoryService.insertMessageAccessoryt(messageAccessory);
            messageAccessoryIds += messageAccessoryId + ",";
        }
        if (audioStr != null && !"".equals(audioStr)) {
            MessageAccessory messageAccessory = new MessageAccessory();
            messageAccessory.setAccessoryType("2");
            messageAccessory.setAccessoryName(audioStr);
            messageAccessory.setAccessoryPath(fileUtil.getShowUrl()+audioStr);
            messageAccessory.setEventId(eventId);
            messageAccessory.setCdate(date);
            Integer messageAccessoryId = messageAccessoryService.insertMessageAccessoryt(messageAccessory);
            messageAccessoryIds += messageAccessoryId + ",";
        }
        if (vedioStr != null && !"".equals(vedioStr)) {
            MessageAccessory messageAccessory = new MessageAccessory();
            messageAccessory.setAccessoryType("3");
            messageAccessory.setAccessoryName(vedioStr);
            messageAccessory.setAccessoryPath(fileUtil.getShowUrl()+vedioStr);
            messageAccessory.setEventId(eventId);
            messageAccessory.setCdate(date);
            Integer messageAccessoryId = messageAccessoryService.insertMessageAccessoryt(messageAccessory);
            messageAccessoryIds += messageAccessoryId + ",";
        }
        // 如果含有附件信息 删最后一位符号
        if (messageAccessoryIds != "" && messageAccessoryIds != null) {
            messageAccessoryIds = messageAccessoryIds.substring(0, messageAccessoryIds.length() - 1);
        }
        return messageAccessoryIds;
    }

    /**
     * findOne
     *
     * @author 姜文
     * @Param
     * @date 2018年2月26日, PM 02:56:42
     */
    public Event findOneByEventId(String id) {
//        String sql = "SELECT * FROM `event` WHERE event_id = '" + id + "'";
        String sql = "SELECT\n" +
                "\te.event_id AS eventId,\n" +
                "\te.emp_id AS empId,\n" +
                "\tse.emp_name AS empName,\n" +
                "\te.event_code AS eventCode,\n" +
                "\te.event_content AS eventContent,\n" +
                "\te.event_title AS eventTitle,\n" +
                "\te.event_location AS eventLocation,\n" +
                "\te.event_type_id AS eventTypeId,\n" +
                "\tkv.val AS val,\n" +
                "\tg.grid_name AS gridName,\n" +
                "e.event_source AS eventSource,\n" +
                "e.grid_id AS gridId,\n" +
                "e.event_report_type AS eventReportType," +
                "\te.lat AS lat,\n" +
                "\te.lng AS lng,\n" +
                "\te.cdate AS cdate,\n" +
                "\te.update_time AS updateTime\n" +
                "FROM\n" +
                "\t`event` e\n" +
                "LEFT JOIN kv kv ON e.event_source = kv.kv_id\n" +
                "LEFT JOIN sys_emp se ON e.emp_id = se.emp_id\n" +
                "LEFT JOIN grid g ON e.grid_id = g.grid_id\n" +
                "WHERE\n" +
                "\tevent_id IN (" + id + ")";
        return queryUtil.query(sql, Event.class).get(0);
    }

    /**
     * findOneById
     *
     * @author 姜文
     * @Param
     * @date 2018年2月26日, PM 02:56:42
     */
    public Event findOneForTypeId(String id) {
        String sql = "SELECT * FROM `event` WHERE event_id = '" + id + "'";
        return queryUtil.query(sql, Event.class).get(0);
    }

    /**
     * 根据事件id查附件
     *
     * @author 王玉涛 王硕
     * @Param
     * @date 2017-11-15 14:50
     */
    public List<MessageAccessory> findByEventId(String eventId) {
        String sql = "SELECT * FROM `message_accessory` where FIND_IN_SET( accessory_id, '" + eventId + "')";
        return queryUtil.query(sql, MessageAccessory.class);
    }

    /**
     * 查询已完成事件数量
     *
     * @return
     * @author yxd
     */
    public List<Event> queryFinishEventChart() {
        String sql = "SELECT\n" +
                "\tt.org_name AS orgName,\n" +
                "\tCOUNT(t.event_id) AS finishEventNum\n" +
                "FROM\n" +
                "\t(\n" +
                "\t\tSELECT DISTINCT\n" +
                "\t\t\te.emp_id,\n" +
                "\t\t\tv.event_id,\n" +
                "\t\t\to.org_name,\n" +
                "\t\t\tv.event_status\n" +
                "\t\tFROM\n" +
                "\t\t\t`event` v,\n" +
                "\t\t\tsys_emp e,\n" +
                "\t\t\tsys_emp_role er,\n" +
                "\t\t\tsys_emp_org eo,\n" +
                "\t\t\tsys_org o\n" +
                "\t\tWHERE\n" +
                "\t\t\tv.emp_id = e.emp_id\n" +
                "\t\tAND e.emp_id = er.emp_id\n" +
                "\t\tAND er.role_id != '19'\n" +
                "\t\tAND eo.org_id = o.org_id\n" +
                "\t\tAND eo.emp_id = e.emp_id\n" +
                "\t\tUNION ALL\n" +
                "\t\t\tSELECT DISTINCT\n" +
                "\t\t\t\tv.emp_id,\n" +
                "\t\t\t\tv.event_id,\n" +
                "\t\t\t\to.org_name,\n" +
                "\t\t\t\tv.event_status\n" +
                "\t\t\tFROM\n" +
                "\t\t\t\t`event` v,\n" +
                "\t\t\t\tsys_emp_role er,\n" +
                "\t\t\t\tgrid_owner go,\n" +
                "\t\t\t\tgrid g,\n" +
                "\t\t\t\tsys_org o\n" +
                "\t\t\tWHERE\n" +
                "\t\t\t\tv.emp_id = er.emp_id\n" +
                "\t\t\tAND er.role_id = '19'\n" +
                "\t\t\tAND er.emp_id = go.emp_id\n" +
                "\t\t\tAND go.grid_id = g.grid_id\n" +
                "\t\t\tAND g.org_id = o.org_id\n" +
                "\t) t\n" +
                "WHERE\n" +
                "\tt.event_status = 1\n" +
                "GROUP BY\n" +
                "\tt.org_name";
        List<Event> list = queryUtil.query(sql.toString(), Event.class);
        return list;
    }

    /**
     * 查询未完成事件数量
     *
     * @return
     * @author yxd
     */
    public List<Event> queryDealEventChart() {
        String sql = "SELECT\n" +
                "\tt.org_name AS orgName,\n" +
                "\tCOUNT(t.event_id) AS dealEventNum\n" +
                "FROM\n" +
                "\t(\n" +
                "\t\tSELECT DISTINCT\n" +
                "\t\t\te.emp_id,\n" +
                "\t\t\tv.event_id,\n" +
                "\t\t\to.org_name,\n" +
                "\t\t\tv.event_status\n" +
                "\t\tFROM\n" +
                "\t\t\t`event` v,\n" +
                "\t\t\tsys_emp e,\n" +
                "\t\t\tsys_emp_role er,\n" +
                "\t\t\tsys_emp_org eo,\n" +
                "\t\t\tsys_org o\n" +
                "\t\tWHERE\n" +
                "\t\t\tv.emp_id = e.emp_id\n" +
                "\t\tAND e.emp_id = er.emp_id\n" +
                "\t\tAND er.role_id != '19'\n" +
                "\t\tAND eo.org_id = o.org_id\n" +
                "\t\tAND eo.emp_id = e.emp_id\n" +
                "\t\tUNION ALL\n" +
                "\t\t\tSELECT DISTINCT\n" +
                "\t\t\t\tv.emp_id,\n" +
                "\t\t\t\tv.event_id,\n" +
                "\t\t\t\to.org_name,\n" +
                "\t\t\t\tv.event_status\n" +
                "\t\t\tFROM\n" +
                "\t\t\t\t`event` v,\n" +
                "\t\t\t\tsys_emp_role er,\n" +
                "\t\t\t\tgrid_owner go,\n" +
                "\t\t\t\tgrid g,\n" +
                "\t\t\t\tsys_org o\n" +
                "\t\t\tWHERE\n" +
                "\t\t\t\tv.emp_id = er.emp_id\n" +
                "\t\t\tAND er.role_id = '19'\n" +
                "\t\t\tAND er.emp_id = go.emp_id\n" +
                "\t\t\tAND go.grid_id = g.grid_id\n" +
                "\t\t\tAND g.org_id = o.org_id\n" +
                "\t) t\n" +
                "WHERE\n" +
                "\tt.event_status = 0\n" +
                "GROUP BY\n" +
                "\tt.org_name";
        List<Event> list = queryUtil.query(sql.toString(), Event.class);
        return list;
    }

    /**
     * 查询所有社区
     *
     * @author yxd
     * @Param
     * @date 2018年1月30日
     */
    public List<Event> findAllDeparts() {
        String sql = "select org_id,org_name from sys_org where org_flag=2 and sts=1";
        return queryUtil.query(sql, Event.class);
    }

    /**
     * 查询网格长完成事件
     *
     * @author yxd
     * @Param
     * @date 2018年1月30日
     */
    public List<Event> findWGEventFinish() {
        String sql = "sELECT\n" +
                "\tt.org_name AS orgName,\n" +
                "\tCOUNT(t.event_id) AS finishEventNum\n" +
                "FROM\n" +
                "\t(\n" +
                "\t\n" +
                "\t\t\tSELECT DISTINCT\n" +
                "\t\t\t\tv.emp_id,\n" +
                "\t\t\t\tv.event_id,\n" +
                "\t\t\t\to.org_name,\n" +
                "\t\t\t\tv.event_status\n" +
                "\t\t\tFROM\n" +
                "\t\t\t\t`event` v,\n" +
                "\t\t\t\tsys_emp_role er,\n" +
                "\t\t\t\tgrid_owner go,\n" +
                "\t\t\t\tgrid g,\n" +
                "\t\t\t\tsys_org o\n" +
                "\t\t\tWHERE\n" +
                "\t\t\t\tv.emp_id = er.emp_id\n" +
                "\t\t\tAND er.role_id = '19'\n" +
                "\t\t\tAND er.emp_id = go.emp_id\n" +
                "\t\t\tAND go.grid_id = g.grid_id\n" +
                "\t\t\tAND g.org_id = o.org_id\n" +
                "\t) t\n" +
                "WHERE\n" +
                "\tt.event_status = 1\n" +
                "GROUP BY\n" +
                "\tt.org_name";
        return queryUtil.query(sql, Event.class);
    }

    /**
     * 查询网格长未完成事件
     *
     * @author yxd
     * @Param
     * @date 2018年1月30日
     */
    public List<Event> findWGEventDeal() {
        String sql = "sELECT\n" +
                "\tt.org_name AS orgName,\n" +
                "\tCOUNT(t.event_id) AS dealEventNum\n" +
                "FROM\n" +
                "\t(\n" +
                "\t\n" +
                "\t\t\tSELECT DISTINCT\n" +
                "\t\t\t\tv.emp_id,\n" +
                "\t\t\t\tv.event_id,\n" +
                "\t\t\t\to.org_name,\n" +
                "\t\t\t\tv.event_status\n" +
                "\t\t\tFROM\n" +
                "\t\t\t\t`event` v,\n" +
                "\t\t\t\tsys_emp_role er,\n" +
                "\t\t\t\tgrid_owner go,\n" +
                "\t\t\t\tgrid g,\n" +
                "\t\t\t\tsys_org o\n" +
                "\t\t\tWHERE\n" +
                "\t\t\t\tv.emp_id = er.emp_id\n" +
                "\t\t\tAND er.role_id = '19'\n" +
                "\t\t\tAND er.emp_id = go.emp_id\n" +
                "\t\t\tAND go.grid_id = g.grid_id\n" +
                "\t\t\tAND g.org_id = o.org_id\n" +
                "\t) t\n" +
                "WHERE\n" +
                "\tt.event_status = 0\n" +
                "GROUP BY\n" +
                "\tt.org_name";
        return queryUtil.query(sql, Event.class);
    }

    /**
     * 查询网格长上报治安事件数
     *
     * @author yxd
     * @Param
     * @date 2018年1月30日
     */
    public List<Event> findSaveEvent() {
        String sql = "SELECT\n" +
                "\tt.org_name AS orgName,\n" +
                "\tCOUNT(t.public_security_id) AS dealEventNum\n" +
                "FROM\n" +
                "\t(\n" +
                "\t\tSELECT DISTINCT\n" +
                "\t\t\tv.emp_id,\n" +
                "\t\t\tv.public_security_id,\n" +
                "\t\t\to.org_name,\n" +
                "\t\t\tv.event_status\n" +
                "\t\tFROM\n" +
                "\t\t\t`public_security` v,\n" +
                "\t\t\tsys_emp_role er,\n" +
                "\t\t\tgrid_owner go,\n" +
                "\t\t\tgrid g,\n" +
                "\t\t\tsys_org o\n" +
                "\t\tWHERE\n" +
                "\t\t\tv.emp_id = er.emp_id\n" +
                "\t\tAND er.role_id = '19'\n" +
                "\t\tAND er.emp_id = go.emp_id\n" +
                "\t\tAND go.grid_id = g.grid_id\n" +
                "\t\tAND g.org_id = o.org_id\n" +
                "\t) t\n" +
                "GROUP BY\n" +
                "\tt.org_name";
        return queryUtil.query(sql, Event.class);
    }

    /**
     * findAll——event
     *
     * @author 姜文
     * @Param
     * @date 2018年3月8日, PM 05:25:03
     */
    public List<Event> findAllForCode() {
        String sql = "SELECT * FROM `event` ORDER BY cdate DESC";
        return queryUtil.query(sql, Event.class);
    }

    /**
     * findAll——PublicSecurity
     *
     * @author 姜文
     * @Param
     * @date 2018年3月8日, PM 05:25:03
     */
    public List<PublicSecurity> findAllForCode2() {
        String sql = "SELECT * FROM `public_security` ORDER BY cdate DESC";
        return queryUtil.query(sql, PublicSecurity.class);
    }

    /**
     * PublicSecurity-findOne
     *
     * @author 姜文
     * @Param
     * @date 2018年3月8日, PM 05:58:56
     */
    public List<PublicSecurity> findOneForPublicSecurity(String publicSecurityId) {
        String sql = "SELECT * FROM `public_security` WHERE public_security_id = '"+publicSecurityId+"'";
        return queryUtil.query(sql, PublicSecurity.class);
    }

    /**
     * PublicSecurity-findOne
     *
     * @author 姜文
     * @Param
     * @date 2018年3月8日, PM 05:58:56
     */
    public Integer updateForPublicSecurity(PublicSecurity publicSecurity) {
        PublicSecurity publicSecurity1 = publicSecurityRepositoty.saveAndFlush(publicSecurity);
        if(publicSecurity1 != null){
            return 1;
        }else {
            return -1;
        }
    }

    /**
     * 事件查询
     *
     * @return
     */
    public List<Event> findAllEvent(Event event) {
        StringBuffer sql = new StringBuffer();
        sql.append("SELECT * FROM `event`");
        if (event.getQueryType() == 4) {
            //系统管理员查询所有事件
            sql.append("where 1=1");
        }else if (event.getQueryType() == 1){
            //网格长查询自己的事件
            sql.append("where 1=1 and emp_id = "+ event.getEmpId());
        }else if (event.getQueryType() == 2){
            //社区信息员或社区干部查询该社区下所有事件
            //根据当前登陆人查询当前登陆人部门下所有人
            String sqlempId = "SELECT GROUP_CONCAT(emp_id) empIds from sys_emp_org where org_id  =(SELECT org_id from sys_emp_org  where emp_id = "+event.getEmpId()+")";
            String empIds = queryUtil.query(sqlempId,SysEmp.class).get(0).getEmpIds();
            //查询部门下所有网格长
            String sqlempId2 = "SELECT GROUP_CONCAT(emp_id) empIds from sys_emp_role where FIND_IN_SET(emp_id,'"+empIds+"') and role_id = 19";
            String empIds2 = queryUtil.query(sqlempId2,SysEmpRole.class).get(0).getEmpIds();
            //查看该社区下所有网格长的事件
            sql.append("where 1=1 and FIND_IN_SET(emp_id,'"+empIds2+"') ");
        }else if (event.getQueryType() == 3){
            //街道信息员或街道干部查询该街道下所有社区下所有事件
            //获取街道下部门集合
            String sqlOrgIds = "SELECT GROUP_CONCAT(org_id) orgIds from sys_org where parent_org_id = (SELECT parent_org_id from sys_org where org_id = (SELECT org_id from sys_emp_org  where emp_id = "+event.getEmpId()+"))  ";
            String orgIds = queryUtil.query(sqlOrgIds,SysOrg.class).get(0).getOrgIds();
            //获取每个部门下的人的集合
            String sqlempIds = "SELECT GROUP_CONCAT(emp_id) empIds from sys_emp_org where FIND_IN_SET(org_id,'"+orgIds+"') ";
            String empIds = queryUtil.query(sqlempIds,SysEmp.class).get(0).getEmpIds();
            //筛选其中为网格员角色的人
            String sqlempIds2 = "SELECT GROUP_CONCAT(emp_id) empIds from sys_emp_role where FIND_IN_SET(emp_id,'"+empIds+"') and role_id = 19";
            String empIds2 = queryUtil.query(sqlempIds2,SysEmp.class).get(0).getEmpIds();
            //查看该街道下所有网格长的事件
            sql.append("where 1=1 and FIND_IN_SET(emp_id,'"+empIds2+"') ");
        }
        if(event.getEventTitle()!=null &&event.getEventTitle()!= ""){
            sql.append(" and event_title like '%"+event.getEventTitle()+"%' ");
        }
        if(event.getEventContent()!=null &&event.getEventContent()!= ""){
            sql.append(" and event_content like '%"+event.getEventContent()+"%' ");
        }
        if(event.getEventLocation()!=null &&event.getEventLocation()!= ""){
            sql.append(" and event_location like '%"+event.getEventLocation()+"%' ");
        }
        if(event.getOnDate()!=null &&event.getOnDate()!= ""){
            sql.append(" and cdate>='"+event.getOnDate()+"'");
        }
        if(event.getOutDate()!=null &&event.getOutDate()!= ""){
            sql.append(" and cdate<='"+event.getOutDate()+" 23:59:59'");

        }

        return queryUtil.query(sql.toString(), Event.class);
    }

    /**
     * 事件列表 sql语句
     *
     * @author 姜文
     * @Param
     * @date 2018年4月2日, AM 09:22:59
     */
    public PageUtil<Event> findAllEventForPage(Event event, Integer page, Integer pageSize) {
        if (null == page || "".equals(page) || "0".equals(page)) {//排空，设置默认值
            page = 1;
        }
        if (null == pageSize || "".equals(pageSize) || "0".equals(pageSize)) {//排空，设置默认值
            pageSize = 10;
        }
        StringBuffer sql = new StringBuffer();
        sql.append("SELECT * FROM `event`");
        if (event.getQueryType() == 4) {
            //系统管理员查询所有事件
            sql.append("where 1=1");
        }else if (event.getQueryType() == 1){
            //网格长查询自己的事件
            sql.append("where 1=1 and emp_id = "+ event.getEmpId());
        }else if (event.getQueryType() == 2){
            //社区信息员或社区干部查询该社区下所有事件
            //根据当前登陆人查询当前登陆人部门下所有人
            String sqlempId = "SELECT GROUP_CONCAT(emp_id) empIds from sys_emp_org where org_id  =(SELECT org_id from sys_emp_org  where emp_id = "+event.getEmpId()+")";
            String empIds = queryUtil.query(sqlempId,SysEmp.class).get(0).getEmpIds();
            //查询部门下所有网格长
            String sqlempId2 = "SELECT GROUP_CONCAT(emp_id) empIds from sys_emp_role where FIND_IN_SET(emp_id,'"+empIds+"') and role_id = 19";
            String empIds2 = queryUtil.query(sqlempId2,SysEmpRole.class).get(0).getEmpIds();
            //查看该社区下所有网格长的事件
            sql.append("where 1=1 and FIND_IN_SET(emp_id,'"+empIds2+"') ");
        }else if (event.getQueryType() == 3){
            //街道信息员或街道干部查询该街道下所有社区下所有事件
            //获取街道下部门集合
            String sqlOrgIds = "SELECT GROUP_CONCAT(org_id) orgIds from sys_org where parent_org_id = (SELECT parent_org_id from sys_org where org_id = (SELECT org_id from sys_emp_org  where emp_id = "+event.getEmpId()+"))  ";
            String orgIds = queryUtil.query(sqlOrgIds,SysOrg.class).get(0).getOrgIds();
            //获取每个部门下的人的集合
            String sqlempIds = "SELECT GROUP_CONCAT(emp_id) empIds from sys_emp_org where FIND_IN_SET(org_id,'"+orgIds+"') ";
            String empIds = queryUtil.query(sqlempIds,SysEmp.class).get(0).getEmpIds();
            //筛选其中为网格员角色的人
            String sqlempIds2 = "SELECT GROUP_CONCAT(emp_id) empIds from sys_emp_role where FIND_IN_SET(emp_id,'"+empIds+"') and role_id = 19";
            String empIds2 = queryUtil.query(sqlempIds2,SysEmp.class).get(0).getEmpIds();
            //查看该街道下所有网格长的事件
            sql.append("where 1=1 and FIND_IN_SET(emp_id,'"+empIds2+"') ");
        }
        if(event.getEventTitle()!=null && event.getEventTitle()!= ""){
            sql.append(" and event_title like '%"+event.getEventTitle()+"%' ");
        }
        if(event.getEventHandleType()!=null && event.getEventHandleType()!= ""){
            if("未处理".equals(event.getEventHandleType())){
                sql.append(" and event_status = '0' AND cdate = update_time  ");
            }
            if("处理中".equals(event.getEventHandleType())){
                sql.append(" and event_status = '0' AND cdate <> update_time ");
            }
            if("处理完".equals(event.getEventHandleType())){
                sql.append(" and event_status = '1' ");
            }
        }
        if(event.getEventCode()!=null && event.getEventCode()!= ""){
            sql.append(" and event_code like '%"+event.getEventCode()+"%' ");
        }
        if(event.getOnDate()!=null &&event.getOnDate()!= ""){
            sql.append(" and cdate>='"+event.getOnDate()+"'");
        }
        if(event.getOutDate()!=null &&event.getOutDate()!= ""){
            sql.append(" and cdate<='"+event.getOutDate()+" 23:59:59'");

        }
        sql.append(" ORDER BY cdate DESC ");
        PageUtil<Event> eventPageUtil = queryUtil.queryByPage(sql.toString(), Event.class, page, pageSize);
        return eventPageUtil;
    }

    /**
     * 查询治安事件
     * @param event
     * @return
     */
    public List<PublicSecurity> publicSecurityEvents(Event event) {
        StringBuffer sql = new StringBuffer();
        sql.append("SELECT * FROM `public_security` ");
        if (event.getQueryType() == 4) {
            //系统管理员查询所有事件
            sql.append("where 1=1");
        }else if (event.getQueryType() == 1){
            //网格长查询自己的事件
            sql.append("where 1=1 and emp_id = "+ event.getEmpId());
        }else if (event.getQueryType() == 2){
            //社区信息员或社区干部查询该社区下所有事件
            //根据当前登陆人查询当前登陆人部门下所有人
            String sqlempId = "SELECT GROUP_CONCAT(emp_id) empIds from sys_emp_org where org_id  =(SELECT org_id from sys_emp_org  where emp_id = "+event.getEmpId()+")";
            String empIds = queryUtil.query(sqlempId,SysEmp.class).get(0).getEmpIds();
            //查询部门下所有网格长
            String sqlempId2 = "SELECT GROUP_CONCAT(emp_id) empIds from sys_emp_role where FIND_IN_SET(emp_id,'"+empIds+"') and role_id = 19";
            String empIds2 = queryUtil.query(sqlempId2,SysEmpRole.class).get(0).getEmpIds();
            //查看该社区下所有网格长的事件
            sql.append("where 1=1 and FIND_IN_SET(emp_id,'"+empIds2+"') ");
        }else if (event.getQueryType() == 3){
            //街道信息员或街道干部查询该街道下所有社区下所有事件
            //获取街道下部门集合
            String sqlOrgIds = "SELECT GROUP_CONCAT(org_id) orgIds from sys_org where parent_org_id = (SELECT parent_org_id from sys_org where org_id = (SELECT org_id from sys_emp_org  where emp_id = "+event.getEmpId()+"))  ";
            String orgIds = queryUtil.query(sqlOrgIds,SysOrg.class).get(0).getOrgIds();
            //获取每个部门下的人的集合
            String sqlempIds = "SELECT GROUP_CONCAT(emp_id) empIds empIds from sys_emp_org where FIND_IN_SET(org_id,'"+orgIds+"') ";
            String empIds = queryUtil.query(sqlempIds,SysEmp.class).get(0).getEmpIds();
            //筛选其中为网格员角色的人
            String sqlempIds2 = "SELECT GROUP_CONCAT(emp_id) empIds from sys_emp_role where FIND_IN_SET(emp_id,'"+empIds+"') and role_id = 19";
            String empIds2 = queryUtil.query(sqlempIds2,SysEmp.class).get(0).getEmpIds();
            //查看该街道下所有网格长的事件
            sql.append("where 1=1 and FIND_IN_SET(emp_id,'"+empIds2+"') ");
        }
        if(event.getEventTitle()!=null &&event.getEventTitle()!= ""){
            sql.append(" and event_title like '%"+event.getEventTitle()+"%' ");
        }
        if(event.getEventContent()!=null &&event.getEventContent()!= ""){
            sql.append(" and event_content like '%"+event.getEventContent()+"%' ");
        }
        if(event.getEventLocation()!=null &&event.getEventLocation()!= ""){
            sql.append(" and event_location like '%"+event.getEventLocation()+"%' ");
        }
        if(event.getOnDate()!=null &&event.getOnDate()!= ""){
            sql.append(" and cdate>='"+event.getOnDate()+"'");
        }
        if(event.getOutDate()!=null &&event.getOutDate()!= ""){
            sql.append(" and cdate<='"+event.getOutDate()+" 23:59:59'");

        }
        return queryUtil.query(sql.toString(), PublicSecurity.class);
    }

    /**
     * 事件列表 根据kv表查询事件来源
     *
     * @author 姜文
     * @Param
     * @date 2018年4月2日, AM 09:22:59
     */
    public KV selectValFromKv(String key){
        String sql = "SELECT val AS val FROM `kv` WHERE kv_id = '"+key+"'";
        return queryUtil.query(sql, KV.class).get(0);
    }

    /**
     * 事件列表 查询事件处理状态 未处理、处理中、处理完
     *
     * @author 姜文
     * @Param
     * @date 2018年4月2日, AM 09:22:59
     */
    public String selectEventHandleType(String eventId){
        String sqlEvent = "SELECT event_status AS eventStatus, cdate AS cdate, update_time AS updateTime FROM `event` WHERE event_id = '"+eventId+"'";
        String sqlDraw = "SELECT * FROM `draw_time` WHERE event_id = '"+eventId+"'";
        Event event = queryUtil.query(sqlEvent, Event.class).get(0);
        if(event.getEventStatus() == 1){
            return "处理完";
        }else{
            int res = event.getCdate().compareTo(event.getUpdateTime());
            if(res == 0){
                return "未处理";
            }else{
                return "处理中";
            }
        }
    }

    /**
     * 事件列表 查看详情
     *
     * @author 姜文
     * @Param
     * @date 2018年4月2日, AM 09:22:59
     */

}
